﻿//************************************************************************//
//文件功能：DataTable函数集合
//创 建 人：薄帅
//创建时间：2011年05月26日
//修 改 人：
//修改时间：
//************************************************************************//

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace ProjBackEnd.Common.Helper
{
    public class DataTableHelper
    {
        /// <summary>
        /// 根据查询条件返回datatable
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="strSelect">条件 Table.Select</param>
        /// <returns>datatable</returns>
        public static DataTable GetDataTableSelect(DataTable dt, string strSelect)
        {
            return GetDataTableSelect(dt, strSelect, "");
        }

        /// <summary>
        /// 根据查询条件返回datatable
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="strSelect">条件 Table.Select</param>
        /// <param name="strSort">排序表达式</param>
        /// <returns>datatable</returns>
        public static DataTable GetDataTableSelect(DataTable dt, string strSelect, string strSort)
        {
            DataRow[] dr = dt.Select(strSelect, strSort);
            DataTable tbl = dt.Clone();
            tbl.BeginLoadData();
            for (int i = 0; i < dr.Length; i++)
            {
                tbl.ImportRow(dr[i]);
            }
            return tbl;
        }

        /// <summary>
        /// 删除除键值和传入的列名数组以外的所有DataTable列
        /// </summary>
        /// <param name="tbl">DataTable</param>
        /// <param name="strColumns">要保留的列</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableColumns(DataTable dataTable, string[] strColumns)
        {
            if (dataTable == null)
                return null;

            DataTable tbl = dataTable.Clone();
            tbl.BeginLoadData();
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                tbl.ImportRow(dataTable.Rows[index]);
            }

            int i;

            List<string> arr = new List<string>();

            foreach (DataColumn col in tbl.Columns)                         //遍历DataTable的列
            {
                i = 0;
                if (!tbl.Columns.CanRemove(col))                            //如果该列不能移除就继续循环
                    continue;

                foreach (string strColumn in strColumns)                    //遍历传入的列名数组
                {
                    if (col.ColumnName.ToUpper() == strColumn.ToUpper())    //如果DataTable的列和传入的列名相同
                    {
                        i++;
                        break;
                    }
                }
                if (i == 0)                                                 //如果DataTable的列不在出入的列名数组里
                    arr.Add(col.ColumnName);                                //获取要删除的列的列名数组
            }
            for (i = 0; i < arr.Count; i++)                                 //遍历要删除的列的列名数组
            {
                tbl.Columns.Remove(arr[i].ToString());                      //删除该列；
            }
            return tbl;
        }

        /// <summary>
        /// 判断DataTable是否改变
        /// </summary>
        /// <param name="filename">转换的文件名</param>
        /// <returns>bool:改变为true否则为false</returns>
        public static bool GetDataTableChanges(DataTable tbl)
        {
            bool bReturn = false;

            foreach (DataRow row in tbl.Rows)
            {
                if (row.RowState != DataRowState.Unchanged)
                {
                    bReturn = true;
                    break;
                }
            }
            return bReturn;
        }

        /// <summary>
        /// 比较两个DataTable是否相同
        /// </summary>
        /// <param name="dtOriginal">原表</param>
        /// <param name="dtNow">更改过的表</param>
        /// <returns>false是表不同，true是表相同</returns>
        public static bool CompareDataTable(DataTable dtOriginal, DataTable dtNow)
        {
            //比较列数是否相同 
            int oriCount = dtOriginal.Columns.Count;
            int nowCount = dtNow.Columns.Count;
            if (oriCount != nowCount)
                return false;
            //比较列名是否相同
            for (int i = 0; i < oriCount; i++)
            {
                if (dtOriginal.Columns[i].ColumnName != dtNow.Columns[i].ColumnName)
                    return false;
            }
            //比较行数是否相同
            int oriRowsCount = dtOriginal.Rows.Count;
            int nowRowsCount = dtNow.Rows.Count;
            if (oriRowsCount != nowRowsCount)
                return false;

            //比较该行的该列的数据是否相同
            foreach (DataRow nRow in dtNow.Rows)
            {
                try
                {
                    dtOriginal.ImportRow(nRow);
                }
                catch
                {
                    continue;
                }
                return false;

            }


            return true;
        }
        /// <summary>
        /// 做交叉表
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="indexcol">索引列</param>
        /// <param name="crosscol">要做交叉的列</param>
        /// <param name="sumcol">合计的列</param>
        /// <returns>返回交叉表</returns>
        public static DataTable ConvertCrossTable(DataTable table, string indexcol, string crosscol, string sumcol)
        {
            if (!table.Columns[sumcol].DataType.IsValueType)
                return table;
            if (table.Rows.Count <= 0)
                return table;

            ArrayList arrindex = new ArrayList();               //定义索引列的动态数组
            ArrayList arrcross = new ArrayList();               //定义交叉列的动态数组

            object obj = null;
            DataRow[] rows = table.Select("", indexcol);        //获取按索引列排序的记录
            foreach (DataRow row in rows)                       //遍历记录,获取索引列的数组
            {
                object objcol = row[indexcol];
                if (!objcol.Equals(obj))                              //判断该行的索引列是否已存在
                {
                    arrindex.Add(objcol);
                    obj = objcol;
                }
            }

            obj = null;
            rows = table.Select("", crosscol);                  //获取按交叉列排序的记录
            foreach (DataRow row in rows)                       //遍历记录,获取交叉列的数组
            {
                object objcol = row[crosscol];
                if (!objcol.Equals(obj))
                {
                    arrcross.Add(objcol);
                    obj = objcol;
                }
            }

            DataTable tbl = new DataTable();                    //定义返回的交叉表
            tbl.Columns.Add(indexcol);
            for (int i = 0; i < arrcross.Count; i++)            //遍历交叉列数组生成新列
            {
                tbl.Columns.Add(arrcross[i].ToString());
            }
            tbl.Columns.Add("SUBTOTAL");                        //新增小计列

            for (int i = 0; i < arrindex.Count; i++)            //遍历索引列数组的记录
            {
                string strIndex = arrindex[i].ToString();       //获取索引列中的记录
                decimal dec = 0;                                //定义小计的变量
                DataRow row = tbl.NewRow();
                row[indexcol] = strIndex;

                for (int j = 0; j < arrcross.Count; j++)        //遍历交叉列数组的记录
                {
                    string strCross = arrcross[j].ToString();
                    row[strCross] = table.Compute("SUM(" + sumcol + ")", indexcol + " = '" + strIndex + "' AND " + crosscol + " = '" + strCross + "'"); //获取以索引列和交叉列过滤的统计列的加和
                    dec = dec + (row[strCross] == DBNull.Value ? 0 : Convert.ToDecimal(row[strCross]));
                }
                row["SUBTOTAL"] = dec;
                tbl.Rows.Add(row);
            }

            return tbl;

        }
    }
}
